package loginmysql;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;
import java.util.TreeMap;

import com.mysql.jdbc.PreparedStatement;
import com.sun.corba.se.impl.encoding.OSFCodeSetRegistry.Entry;

import logic.Categoria;
import logic.Item;

public class MysqlLogin {
	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	// static final String DB_URL = "jdbc:mysql://localhost/Cot";
	static final String DB_URL = "jdbc:mysql://localhost/cot";
	// Database credentials
	// static final String USER = "root";
	static final String USER = "root";
	static final String PASS = "Beyer9499";
	static String retString = "e";

	// static ArrayList<Item> items = new ArrayList<Item>();
	static ArrayList<String> unidadesal = new ArrayList<String>();
	static ArrayList<String> mailsAl = new ArrayList<String>();
	static Map<Integer, String> localidadesal = new TreeMap<Integer, String>();
	static ArrayList<String> login = new ArrayList<String>();
	static ArrayList<Categoria> cats = new ArrayList<Categoria>();
	static ArrayList<Item> items = new ArrayList<Item>();

	static int numCats;
	static java.sql.Connection conn = null;
	static java.sql.Statement stmt = null;
	static java.sql.ResultSet rs, rs2, rs3;
	static int formId;

	public static void connect() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(DB_URL, USER, PASS);
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void disconnect() {
		try {
			if (stmt != null)
				stmt.close();
		} catch (SQLException se2) {
		}// nothing we can do
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException se) {
			se.printStackTrace();
		}// end finally try
	}

	public static Map<Integer, String> getLocalidades() {
		try {
			localidadesal.clear();

			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT lid, info, estado FROM localidad";
			ResultSet rs3 = stmt.executeQuery(sql);
			while (rs3.next()) {
				int id = rs3.getInt("lid");
				String info = rs3.getString("info");
				int estado = rs3.getInt("estado");
				if (estado == 1) {
					localidadesal.put(id, info);
				}
			}
			rs3.close();
			stmt.close();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}// end try
		System.out.println("Goodbye!");
		return localidadesal;

	}

	public static ArrayList<String> getUnidades() {
		try {
			unidadesal.clear();

			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT numero FROM unidades";
			ResultSet rs3 = stmt.executeQuery(sql);
			while (rs3.next()) {
				String unidad = rs3.getString("numero");
				unidadesal.add(unidad);
				// System.out.println(unidad);
			}
			rs3.close();
			stmt.close();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}// end try
		System.out.println("Goodbye!");
		return unidadesal;
	}
	
	public static ArrayList<String> getMails() {
		try {
			mailsAl.clear();

			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT mail FROM mails";
			ResultSet rs3 = stmt.executeQuery(sql);
			while (rs3.next()) {
				String mail = rs3.getString("mail");
				mailsAl.add(mail);
				// System.out.println(unidad);
			}
			rs3.close();
			stmt.close();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}// end try
		System.out.println("Goodbye!");
		return mailsAl;
	}

	public static void insert(String sql) throws SQLException {

			// STEP 5: Excute query
			System.out.print("\nInserting records into table...");
			stmt = conn.createStatement();

			stmt.executeUpdate(sql);

			System.out.println(" SUCCESS!\n");

	}
	public static int getLastId(String table, String column) throws SQLException {
		int last = 0;
		
			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT "+column+" FROM "+table;
			ResultSet rs3 = stmt.executeQuery(sql);
			while (rs3.last()) {
				last = rs3.getInt(column);
			}
			rs3.close();
			stmt.close();
		System.out.println("Goodbye!");
		return last;
	}

	public static int getFormId() {
		try {

			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT formid FROM form";
			ResultSet rs3 = stmt.executeQuery(sql);
			if (rs3.last()) {
				formId = rs3.getInt("formid");
			}
			rs3.close();
			stmt.close();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();

		}// end try
		System.out.println(formId);
		return formId;
	}
	public static int getCatId(String nameCat) {
		int catId = 0;
		try {
			
			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT cid FROM categoria where info='"+nameCat+"'";
			ResultSet rs3 = stmt.executeQuery(sql);
			if (rs3.last()) {
				catId = rs3.getInt("cid");
			}
			rs3.close();
			stmt.close();
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();

		}// end try
		System.out.println(catId);
		return catId;
	}

	public static void clearCats() {
		cats.clear();
	}
	

	public static ArrayList<Categoria> getCats() {
		try {
			// items.clear();
			// STEP 2: Register JDBC driver
			Categoria categoria = null;
			// STEP 3: Open a connection
			System.out.println("Connecting to database...");

			// STEP 4: Execute a query
			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql, sql2;
			sql2 = "SELECT cid, info, activo FROM categoria";

			rs2 = stmt.executeQuery(sql2);
			while (rs2.next()) {
				int id = rs2.getInt("cid");
				numCats = id;
				int active = rs2.getInt("activo");
				String info = rs2.getString("info");
				if (active == 1) {
					categoria = new Categoria(id, info);
					cats.add(categoria);
				}
				System.out.println("cats size " + cats.size());
			}
			for (Categoria cat : cats) {
				sql = "SELECT iid, info, activo, cid FROM item where cid="
						+ cat.getId();
				rs3 = stmt.executeQuery(sql);
				ArrayList<Item> items = new ArrayList<Item>();
				// STEP 5: Extract data from result set
				while (rs3.next()) {
					String info = rs3.getString("info");
					int id = rs3.getInt("iid");
					int activo = rs3.getInt("activo");
					if (activo == 1) {
						Item it = new Item(id, info);
						items.add(it);
						cat.setItems(items);
						System.out.println(cat.getInfo() + "  " + info);
					}

				}
			}
			// STEP 6: Clean-up environment
			rs2.close();
			rs3.close();
			stmt.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}// end try
		System.out.println("Goodbye!");
		return cats;
	}
	
	public static ArrayList<String> login(String username, String pass) {

		try {
			login.clear();
			// STEP 2: Register JDBC driver
			// STEP 4: Execute a query
			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;
			sql = "SELECT id, nombre, apellido, user, password, Privilegios FROM funcionarios where user=? and password=?";
			java.sql.PreparedStatement prep = conn.prepareStatement(sql);
			prep.setString(1, username);
			prep.setString(2, pass);

			ResultSet rs = prep.executeQuery();
			retString = "e";// resetea retstring
			boolean notLogged = true;
			if (rs.next()) {
				login.clear();
				String first = rs.getString("nombre");
				String last = rs.getString("apellido");
				retString = first + " " + last;
				int id = rs.getInt("id");
				int priv = rs.getInt("Privilegios");
				login.add(retString);
				login.add(String.valueOf(id));
				login.add(String.valueOf(priv));
			} else {
				System.out.println("No encontro usuario" + "  " + retString);
				login.add("e");
				login.add("0");
				login.add("0");
			}

			// STEP 5: Extract data from result set
			/*
			 * while (rs.next()&&notLogged) { // Retrieve by column name
			 * login.clear(); int id = rs.getInt("id"); String first =
			 * rs.getString("nombre"); String last = rs.getString("apellido");
			 * String user = rs.getString("user"); String password =
			 * rs.getString("password");
			 * 
			 * if (user.equalsIgnoreCase(username)) {
			 * System.out.println("Usuario igual"); if (password.equals(pass)) {
			 * retString = first + " " + last;
			 * System.out.println("Contrasena "+retString);
			 * login.add(retString); login.add(String.valueOf(id));
			 * notLogged=false; } } else { System.out
			 * .println("No encontro usuario" + "  " + retString);
			 * login.add("e"); login.add("0"); } }
			 */
			// STEP 6: Clean-up environment
			rs.close();
			stmt.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}
		System.out.println("Goodbye!");
		return login;
	}

	/*
	 * Devuelve una lista completa de todos los items activos
	 */
	public static ArrayList<Item> getItems() {
		try {

			// STEP 4: Execute a query
			System.out.println("Creating statement...");
			stmt = conn.createStatement();
			String sql;

			sql = "SELECT iid, info, activo FROM item";
			rs3 = stmt.executeQuery(sql);

			// STEP 5: Extract data from result set
			while (rs3.next()) {
				String info = rs3.getString("info");
				int id = rs3.getInt("iid");
				int activo = rs3.getInt("activo");
				if (activo == 1) {
					Item it = new Item(id, info);
					items.add(it);
				}

			}
			rs2.close();
			rs3.close();
			stmt.close();
		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		}// end try
		return items;
	}
}// end FirstExample